var express = require("express");
const mysql = require("mysql");
const util = require("util");
var router = express.Router();
//const logger = require('../logger')
// 创建数据库连接
const configs = require("../config");
const db = mysql.createConnection({
  host: configs.mysql.host,
  port: configs.mysql.port,
  user: configs.mysql.user,
  password: configs.mysql.password,
  database: configs.mysql.database,
  useConnectionPooling: true,
  debug: true,
});

// 将 db.query 转换为 Promise
const query2 = util.promisify(db.query).bind(db);
const pool = mysql.createPool({
  connectionLimit: 10,
  host: configs.mysql.host,
  port: configs.mysql.port,
  user: configs.mysql.user,
  password: configs.mysql.password,
  database: configs.mysql.database,
  // 其他连接设置...
});

// 使用连接池执行查询
async function query(sql) {
  return new Promise((resolve, reject) => {
    pool.getConnection((err, db) => {
      if (err) {
        reject(err);
        return;
      }
      db.query(sql, (error, results) => {
        // 释放连接回连接池
        db.release();
        if (error) {
          reject(error);
          return;
        }
        resolve(results);
      });
    });
  });
}

/* GET home page. */
router.get("/", function (req, res, next) {
  res.render("index", {
    title: "Express",
  });
});
router.post("/insert", (req, res) => {
  let { tableName, data } = req.body;
  insertAndReturn(tableName, data, (result) => {
    //logger.error(`result` + result)
    res.json(result);
    res.send(result);
  });
});
router.post("/insert2", (req, res) => {
  let { tableName, data } = req.body;

  // 从连接池中获取一个连接
  pool.getConnection(function (err, connection) {
    if (err) {
      throw err;
    }

    // 开始事务
    connection.beginTransaction(function (err) {
      if (err) {
        connection.release(); // 释放连接
        throw err;
      }

      // 定义插入多条数据的SQL语句
      let sql = `INSERT INTO ${tableName} (id, usercode, productcode, productname, FNSKU, ASIN, productnum, productposition, productcude, requirements, notes, inputtime, \`type\`, ygnum, ybjyqsl, sjhbcpsl, xzsl, xh, xb, zcdxccinch, zllbx,bz) VALUES ?`;

      // 将data转换为插入的值数组
      let values = data.map((item) => [
        item.id,
        item.usercode,
        item.productcode,
        item.productname,
        item.FNSKU,
        item.ASIN,
        item.productnum,
        item.productposition,
        item.productcude,
        item.requirements,
        item.notes,
        item.inputtime,
        item.type,
        item.ygnum,
        item.ybjyqsl,
        item.sjhbcpsl,
        item.xzsl,
        item.xh,
        item.xb,
        item.zcdxccinch,
        item.zllbx,
        item.bz,
      ]);

      // 执行插入操作
      connection.query(sql, [values], function (err, result) {
        if (err) {
          // 回滚事务
          connection.rollback(function () {
            connection.release(); // 释放连接
            throw err;
          });
        }

        // 提交事务
        connection.commit(function (err) {
          if (err) {
            // 回滚事务
            connection.rollback(function () {
              connection.release(); // 释放连接
              throw err;
            });
          }

          // 释放连接
          connection.release();

          // 返回结果
          res.json(result);
        });
      });
    });
  });
});
router.put("/update", (req, res) => {
  let { tableName, data, conditions } = req.body;
  updateAndReturnCount(tableName, data, conditions, (updatedCount) => {
    res.json({
      updatedCount,
    });
    res.send(updatedCount);
  });
});

router.delete("/delete", (req, res) => {
  let { tableName, conditions } = req.body;
  deleteRows(tableName, conditions, (affectedRows) => {
    res.json({
      affectedRows,
    });
    res.send(affectedRows);
  });
});
router.get("/select", async (req, res) => {
  try {
    // 使用请求查询字符串作为参数
    let tableName = req.query.tableName;
    let fields = req.query.fields === "*" ? ["*"] : req.query.fields.split(",");
    let conditions = req.query.conditions
      ? JSON.parse(req.query.conditions)
      : null;
    console.log("conditions: ", conditions);
    let sort = req.query.sort ? JSON.parse(req.query.sort) : null;
    let pagination = req.query.pagination
      ? JSON.parse(req.query.pagination)
      : null;

    let result = await select(tableName, fields, conditions, pagination, sort);

    res.json(result);
  } catch (err) {
    console.error(err);
    //logger.error(`select:` + err)
    res.status(500).send("Server error");
  }
});
router.post("/sql", async (req, res) => {
  try {
    // 使用请求查询字符串作为参数
    let { sql } = req.body;

    let result = await selectsql(sql);

    res.json(result);
  } catch (err) {
    console.error(err);
    //logger.error(`sql:` + err)
    res.status(500).send("Server error");
  }
});

router.get("/newselet", async (req, res) => {
  // 从连接池获取连接
  pool.getConnection((err, connection) => {
    if (err) {
      console.error("Error getting connection from pool: " + err.stack);
      return;
    }
    // 查询leokc表按usercode分组
    const query =
      "SELECT usercode,(select max(inputtime)  from leokc l2 where usercode = l.usercode) as maxinputtime from leokc l group by usercode order by maxinputtime desc,usercode asc";
    connection.query(query, (err, results) => {
      if (err) {
        console.error("Error executing query: " + err.stack);
        connection.release(); // 释放连接
        return;
      }
      // 创建一个数组来存储所有子查询的Promise
      const subQueryPromises = results.map((row) => {
        const usercode = row.usercode;
        // 根据每行的usercode查询数据
        const subQuery = `select *,sum(productnum) as sumnum,(SELECT sum(productnum) as sunnum FROM  leokc where type = 0 and  usercode = kc.usercode and  productcode = kc.productcode group by productcode) as zero,(SELECT sum(productnum) as sunnum FROM  leokc where type = 1 and  usercode = kc.usercode and  productcode = kc.productcode group by productcode) as one,(SELECT sum(productnum) as sunnum FROM  leokc where type = 2 and  usercode = kc.usercode and  productcode = kc.productcode group by productcode) as two,(SELECT SUM(productnum) from leokc where usercode = kc.usercode and  productcode = kc.productcode and type=0) as productnum,(SELECT SUM(productnum) from leokc where usercode = kc.usercode and  productcode = kc.productcode and type=1) as cproductnum from leokc kc WHERE usercode = '${usercode}' GROUP BY productcode `;
        return new Promise((resolve, reject) => {
          connection.query(subQuery, (err, subResults) => {
            if (err) {
              row.subset = err;
              reject(err);
            } else {
              // 将子集结果加入第一个结果的子集
              row.subset = subResults;
              resolve();
            }
          });
        });
      });

      // 等待所有子查询完成
      Promise.all(subQueryPromises)
        .then(() => {
          // 释放连接
          connection.release();
          // 返回结果
          res.json(results);
        })
        .catch((err) => {
          console.error("Error executing sub query: " + err.stack);
          // 释放连接
          connection.release();
          // 返回错误信息
          res.status(500).json({ error: "Internal Server Error" });
        });
    });
  });
});

const multer = require("multer");
const upload = multer({
  dest: "uploads/",
});
const xlsx = require("xlsx");

function uuid() {
  var d = new Date().getTime();

  var uuid = "xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx".replace(
    /[xy]/g,
    function (c) {
      var r = (d + Math.random() * 16) % 16 | 0;
      d = Math.floor(d / 16);
      return (c == "x" ? r : (r & 0x3) | 0x8).toString(16);
    }
  );

  return uuid;
}
router.post("/upload2", upload.single("file"), (req, res) => {
  if (!req.file) {
    return res.status(400).send("No file uploaded.");
  }
  // 可以在此处添加文件处理逻辑
  // 在上传接口中添加...
  const workbook = xlsx.readFile(req.file.path);
  const sheetName = workbook.SheetNames[0];
  const worksheet = workbook.Sheets[sheetName];
  const data = xlsx.utils.sheet_to_json(worksheet);
  let newArrayWithId = data.map((item) => {
    //logger.error(`xlsx:` + JSON.stringify(item))
    return {
      id: uuid(), // 添加 id 属性
      usercode: item["客户编号"] !== undefined ? item["客户编号"] : null,
      productcode: item["产品代码"] !== undefined ? item["产品代码"] : null,
      productname: item["产品名称"] !== undefined ? item["产品名称"] : null,
      FNSKU: item["产品fnsku"] !== undefined ? item["产品fnsku"] : null,
      ASIN: item["产品asin"] !== undefined ? item["产品asin"] : null,
      productnum: item["数量"] !== undefined ? item["数量"] : null,
      requirements: item["要求"],
      notes: item["备注"],
      inputtime: new Date(),
      type: 2,
    };
  });
  let nullKeys = [];

  newArrayWithId.forEach((item) => {
    for (const key in item) {
      if (item[key] === null && key !== "FNSKU" && key !== "ASIN") {
        nullKeys.push(key);
      }
    }
  });
  let r = {};
  // 去重
  nullKeys = [...new Set(nullKeys)];
  if (nullKeys.length > 0) {
    r = {
      code: 500,
      res: nullKeys,
    };
    res.json(r);
    res.send(r);
  } else {
    const sql =
      "INSERT INTO leokc (id,usercode, productcode, productname,FNSKU,ASIN,productnum,requirements,notes,inputtime,type) VALUES ?";
    let values = newArrayWithId.map((item) => [
      item.id,
      item.usercode,
      item.productcode,
      item.productname,
      item.FNSKU,
      item.ASIN,
      item.productnum,
      item.requirements,
      item.notes,
      item.inputtime,
      item.type,
    ]);

    query2(sql, [values], function (err, result) {
      if (err) {
        // logger.error(`upload` + err)
      }
      console.log("Number of records inserted: " + result.affectedRows);
      r = {
        code: 200,
        res: result.affectedRows,
      };
      res.json(r);
      res.send(r);
    });
  }
});
router.post("/upload", upload.single("file"), (req, res) => {
  if (!req.file) {
    return res.status(400).send("No file uploaded.");
  }
  // 可以在此处添加文件处理逻辑
  // 在上传接口中添加...
  const workbook = xlsx.readFile(req.file.path);
  const sheetName = workbook.SheetNames[0];
  const worksheet = workbook.Sheets[sheetName];
  const data = xlsx.utils.sheet_to_json(worksheet);
  var startIndex = data.findIndex(function (item) {
    return item["Mayah海外仓FBA退件预约入库申请表"] === "客户编号";
  });

  // 找出结束元素的索引
  var endIndex = data.findIndex(function (item) {
    return (
      "__EMPTY_6" in item &&
      item["__EMPTY_6"] === "整箱出库，不足一箱的等待剩余移除凑整一箱"
    );
  });

  // 提取这个范围内的元素（注意，slice 不包括 endIndex 位置的元素）
  var newArray = data.slice(startIndex + 1, endIndex);

  let newArrayWithId = newArray.map((item) => {
    //logger.error(`xlsx:` + JSON.stringify(item))
    return {
      id: uuid(), // 添加 id 属性
      usercode: item["Mayah海外仓FBA退件预约入库申请表"],
      productcode: item["__EMPTY"],
      productname: item["__EMPTY_1"],
      FNSKU: item["__EMPTY_2"] !== undefined ? item["__EMPTY_2"] : null,
      ASIN: item["__EMPTY_3"] !== undefined ? item["__EMPTY_3"] : null,
      productnum: item["__EMPTY_4"],
      requirements: item["__EMPTY_5"],
      notes: item["__EMPTY_6"],
      inputtime: new Date(),
      type: 2,
    };
  });
  const sql =
    "INSERT INTO leokc (id,usercode, productcode, productname,FNSKU,ASIN,productnum,requirements,notes,inputtime,type) VALUES ?";
  let values = newArrayWithId.map((item) => [
    item.id,
    item.usercode,
    item.productcode,
    item.productname,
    item.FNSKU,
    item.ASIN,
    item.productnum,
    item.requirements,
    item.notes,
    item.inputtime,
    item.type,
  ]);

  query2(sql, [values], function (err, result) {
    if (err) {
      // logger.error(`upload` + err)
    }
    console.log("Number of records inserted: " + result.affectedRows);
    res.json(result.affectedRows);
    res.send(result.affectedRows);
  });
});

function insertAndReturn(tableName, data, callback) {
  // 插入数据
  const insertQuery = `INSERT INTO ${tableName} SET ?`;
  query2(insertQuery, data, (error, insertResults) => {
    if (error) {
      // logger.error(`insertAndReturn：` + error)
    }

    // 获取刚插入的记录的 ID
    const lastId = insertResults.insertId;

    callback(lastId);
  });
}

function updateAndReturnCount(tableName, data, conditions, callback) {
  const conditionString = Object.keys(conditions)
    .map((key) => `${key} = ?`)
    .join(" AND ");
  const updatequery = `UPDATE ${tableName} SET ? WHERE ${conditionString}`;
  query2(
    updatequery,
    [data, ...Object.values(conditions)],
    (error, results, fields) => {
      if (error) throw error;
      callback(results.affectedRows); // 返回更新的条数
    }
  );
}

function deleteRecord(tableName, conditions, callback) {
  const conditionString = Object.keys(conditions)
    .map((key) => `${key} = ?`)
    .join(" AND ");
  const deletequery = `DELETE FROM ${tableName} WHERE ${conditionString}`;
  query2(
    quedeletequeryy,
    Object.values(conditions),
    (error, results, fields) => {
      if (error) throw error;
      callback(results);
    }
  );
}

//select('users', ['name', 'email'], [{ key: 'status', value: 'active' }], {page: 2, pageSize: 20});
async function select(tableName, fields, conditions, pagination, sort) {
  let sqlquery = `SELECT ${fields.join(",")} FROM ${tableName}`;
  if (conditions) {
    let conditionsStr = conditions
      .map((cond) => {
        let operator = cond.operator || "=";
        return `${cond.key} ${operator} ${mysql.escape(cond.value)}`;
      })
      .join(" AND ");
    sqlquery += ` WHERE ${conditionsStr}`;
  }
  if (sort) {
    sqlquery += ` ORDER BY ${sort.field} ${sort.direction}`;
  }
  // 如果 pagination 参数被传入，那么添加 LIMIT 和 OFFSET 子句
  if (pagination) {
    let page = pagination.page || 1;
    let pageSize = pagination.pageSize || 10;
    let offset = (page - 1) * pageSize;
    sqlquery += ` LIMIT ${pageSize} OFFSET ${offset}`;
  }
  try {
    const rows = await query(sqlquery);
    return rows;
  } catch (err) {
    console.error("Database query error: ", err);
    throw err;
  }
}
//通用查询
async function selectsql(sql) {
  let sqlquery = sql;

  try {
    const rows = await query(sqlquery);
    return rows;
  } catch (err) {
    console.error("Database query error: ", err);
    throw err;
  }
}

async function newselet() {
  try {
    // const rows = await query('select usercode from leokc group by usercode');
    // results.forEach((row) => {
    //   const subQuery =await query('select productcode,sum(productnum) from leokc where usercode = \''+row.usercode+'\' GROUP BY productcode');
    //   row.subset = subQuery;
    // });

    pool.getConnection((err, db) => {
      if (err) {
        reject(err);
        return;
      }
      const rows = db.query(
        "select usercode from leokc group by usercode",
        (error, results) => {
          // 释放连接回连接池
          db.release();
          if (error) {
            return error;
          }
          return results;
        }
      );
    });
  } catch (err) {
    console.error("Database query error: ", err);
    throw err;
  }
}
module.exports = router;
